Q Please review all reading and viewings prior to starting your discussions and homework. This activity addresses the following module outcomes: • MO4: Manually prepare all eight types of decision-making budgets. (CO4) Introduction For this class, you will complete a Budget Project. This project has three parts that will be due in Module 5, 6, and 7. Below is the scenario and the required elements laid out. Background McMillan Toys is a worldwide toy manufacturer who is considering expanding into new markets and developing new toys over the next year. Michael Dorsey, the CEO of McMillian Toys hired you as a cost accountant. He would like you improve the budgeting process but doesn’t know how. You suggest implementing Excel spreadsheets so that when management makes last-minute changes to sales and other forecasts, all the other numbers will automatically update. Michael Dorsey agrees with your recommendation and gave you the project to prepare the master budget for the first quarter of the upcoming year along with a budgeted income statement. He reminded you that a complete master budget has the following budgets included: Sales Budget, Production Budget, Direct Materials Purchases Budget, Direct Labor Budget, Manufacturing Overhead Budget, Selling & Administrative Expense Budget, and Cost of Goods Manufactured Budget. He also requested a budgeted income statement and informed you that the income tax expense is 30%. You have been working with various departments to gather financial data to assemble all eight budgets into a master budget using Excel so that you can connect all the budgets together. Preparation: 1. Review the data provided in the word file titled Budget Project Background, found at this link Download this link. 2. Download the Excel templates you will use to prepare the master budget found at this link Download this link. The Project M5A3: Part 1 of the Budget Project: This part of the project is due at the end of Module 5. 1. Starting with the sales budget, use the data provided to determine total sales for the entire quarter (including any additional months necessary to compute first quarter sales). 2. Using data provided, complete first four budgets only for the first quarter. (Production Budget, Direct Materials Purchases Budget and Direct Labor Budget). The remaining budgets will be completed in Part 2 during Module 6. 3. Use Excel formulas to perform calculations and integrate each budget so changes to one particular budget are automatically updated in the remaining budgets. Submit this part of the assignment in the M5A3 drop box within Module 5. This part of the project will be graded using the ACC360 M5A3 Budget Project Rubric. M6A3: Part 2 of the budget project: This part of the project will be due at the end of Module 6. In this phase of the budgeting project you will be linking, using Excel formulas, all eight budgets together. Here are the steps: 1. Make sure your budgeted numbers for each budget from Part 1 are accurate based on your instructor’s feedback. 2. Starting with the sales budget, enter formulas in the appropriate cells to multiply the sales information by month and to add the sales across to get the total quarter column and row totals. When you insert the formulas, you should get the same accurate budget numbers from step 1. 3. Taking the Sales in Units from the sales budget for each month, use formulas to transfer that information to the row located in the first row of the production budget. Now, take the appropriate cells within the production budget and, using formulas, get the quarterly totals. You should have formulas to get column and row totals. 4. Once you have created the Excel formulas for the production budget, then the direct materials budget, direct labor budget, manufacturing overhead budget, selling budget, and administrative budgets can be linked using a similar process from steps 2 and 3. Within each budget, you will have formulas to multiple totals and formulas adding information to get overall totals. Each of these budgets should have formulas to calculate column and row totals. 5. Once you have inserted all the formulas in the first 6 budgets (steps 2, 3, 4), you must insert formulas into the cost of goods manufactured budget and the budgeted income statement. When you complete the linked formulas for the cost of goods manufactured budget and the budgeted income statement, then those budgets should be completed. Your goal is to link all the budgets together so if any number in the sales budget is changed, all seven budgets after the sales budget will also change; all the way to the budgeted income statement. This is how the budget project will be assessed: Part 1 will be reviewed for accuracy in the totals, and to ensure all calculations are correct. Part 2 will be reviewed to ensure all eight budgets are linked together within one Excel workbook. To make sure all eight budgets are linked together, the sales forecast and some cost information will be altered. If the formulas were entered correctly, all other budgets will automatically change to reflect the new sales totals. This part of the budget project will be graded on how well all formulas have been linked together and the accuracy of the totals. • You can design the formulas however you see fit; just make sure they work. • You will submit this part of the assignment in the M6A3 drop box within Module 6. • This part of the project will be graded using the ACC360 M6A3 Budget Project Rubric. M7A3: Part 3 of the Budget Project: This part of the project will be due at the end of Module 6. Prepare a written report reflecting on your experiences regarding the challenges, difficulties, and implementation of creating a budget. What factors should be considered when developing a master budget? Why? How, should factors like employment rates, inflation, competition, suppliers, and historical data be reflected when developing a master budget? Discuss how a master budget assists with decision making, strategic planning, and achieving the overall goals of cost accounting. Your response should be at least 750 words and provide personal insight on the application of the budgeting process. Your response should include professional insight (or research into the budgeting process), appropriate references and formatting, organization, cohesiveness, and proper editing. • The assignment must be in APA format; 12-point font, 1-inch margins, and double spaced. • A minimum of at least 1 reference is required. The citations and reference(s) should be in APA format. • Submit this part of the assignment as a WORD document in the M7A3 drop box within Module 7. This part of the project will be graded using the ACC360 M7A3 Budget Project Rubric. Evaluation This assignment will be graded using the rubric displayed below. Please review this rubric prior to beginning your work. You can also access the rubric on the Rubrics page within the Start Here module. The Budgeting Project consists of three parts for a total of 20% of your total grade. This part of the budget project is worth 6% of your total grade. Return to the Top of the Page Rubric ACC360_M5A3_BUDGET_PROJECT_RUBRIC ACC360_M5A3_BUDGET_PROJECT_RUBRIC Criteria Ratings Pts This criterion is linked to a Learning OutcomeSales Budget Step 1: Entering sales data into spreadsheet and accurately calculating totals 25 pts Exemplary The correct data is accurately entered. All required calculations are performed using Excel formulas. 21 pts Satisfactory Some data is inaccurate or totals are incorrect. Excel formulas were mostly used. 16 pts Minimally Proficient Data is mostly inaccurate, Excel formulas were not used, data is not linked to other budgets 0 pts Unacceptable No Budget was created or contains no correct elements. 25 pts This criterion is linked to a Learning OutcomeProduction Budget Step 1: Entering production data into spreadsheet, accurately calculating totals, linking data to Sales Budget 25 pts Exemplary The correct data is accurately entered. All required calculations are performed using Excel formulas. Data is linked to other budgets as necessary. 21 pts Satisfactory Some data is inaccurate or totals are incorrect. Excel formulas were mostly used. Data is mostly linked to other budgets as necessary 16 pts Minimally Proficient Data is mostly inaccurate, Excel formulas were not used, data is not linked to other budgets 0 pts Unacceptable Budget data was not was created or contains no correct elements. 25 pts This criterion is linked to a Learning OutcomeDirect Materials Purchases Budget Step 1: Entering direct materials purchases data into spreadsheet, accurately calculating totals, linking data to other budgets as necessary 25 pts Exemplary The correct data is accurately entered. All required calculations are performed using Excel formulas. Data is linked to other budgets as necessary. 21 pts Satisfactory Some data is inaccurate or totals are incorrect. Excel formulas were mostly used. Data is mostly linked to other budgets as necessary 16 pts Minimally Proficient Data is mostly inaccurate, Excel formulas were not used, data is not linked to other budgets 0 pts Unacceptable Budget data was not was created or contains no correct elements. 25 pts This criterion is linked to a Learning OutcomeDirect Labor Budget Step 1: Entering direct labor data Into spreadsheet, accurately calculating totals, linking data to other budgets as necessary 25 pts Exemplary The correct data is accurately entered. All required calculations are performed using Excel formulas. Data is linked to other budgets as necessary. 21 pts Satisfactory Some data is inaccurate or totals are incorrect. Excel formulas were mostly used. Data is mostly linked to other budgets as necessary 16 pts Minimally Proficient Data is mostly inaccurate, Excel formulas were not used, data is not linked to other budgets 0 pts Unacceptable No Budget was created or contains no correct elements. 25 pts Total Points: 100 PreviousNext